package bD;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BDConnect {
	private Connection conn;
	private Statement stm;
	private String tipo;
	
	
	public BDConnect() throws SQLException, ClassNotFoundException {
		Class.forName("org.sqlite.JDBC");
		this.conn = DriverManager.getConnection("jdbc:sqlite:BD.db");
		this.stm = this.conn.createStatement();
	}
	
	
	public void criarTabela() {
		try {
			this.stm.executeUpdate("DROP TABLE IF EXISTS PlacaMae");
			this.stm.executeUpdate("DROP TABLE IF EXISTS PlacaVideo");
			this.stm.executeUpdate("DROP TABLE IF EXISTS Processador");
			this.stm.executeUpdate("CREATE TABLE PlacaMae (" +
					"id integer PRIMARY KEY NOT NULL," +
					"marca varchar(10) NOT NULL,"+
					"fabricante varchar(20) NOT NULL,"+
					"modelo varchar(20) NOT NULL," +
					"preco double NOT NULL," +
					"socketPro varchar(20) NOT NULL,"+
					"socketPv varchar(20) NOT NULL"+
					");");
			this.stm.executeUpdate("CREATE TABLE Processador (" +
					"id integer PRIMARY KEY NOT NULL,"+
					"marca varchar(20) NOT NULL," +
					"modelo varchar(20) NOT NULL," +
					"socket varchar(20) NOT NULL," +
					"preco double NOT NULL," +
					"qualidade double NOT NULL"+
					");");
			this.stm.executeUpdate("CREATE TABLE PlacaVideo ("+
					"id integer PRIMARY KEY NOT NULL,"+
					"marca varchar(20) NOT NULL,"+
					"fabricante varchar(20) NOT NULL,"+
					"modelo varchar(20) NOT NULL,"+
					"preco double NOT NULL,"+
					"socket varchar(20) NOT NULL,"+
					"qualidade double NOT NULL"+
					");");
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		adicionarPlacaVideo();
		adicionarPlacaMae();
		adicionarProcessador();
	}
	
	public void setTipo(String tipo){
		this.tipo = tipo;
	}
	
	public void adicionarPlacaVideo() {
		try {
	        BufferedReader in = new BufferedReader(new FileReader("placaVideo.txt"));
	            String str;
	            String entrada[];
	            int i=0;
	            while (in.ready()) {
	                str = in.readLine();
	                entrada = str.split(" ");
	                try {
						this.stm.executeUpdate("INSERT INTO PlacaVideo VALUES (" +
							(i) + "," +
							"\"" + entrada[0] + "\","+ 
							"\"" + entrada[1] + "\"," +
							"\"" + entrada[2] + "\"," +
							entrada[3] + "," +
							"\"" + entrada[4] + "\"," +
							entrada[5] + "" +
							")");
	               		} catch (SQLException e) {
	               			e.printStackTrace();
					}
	                i++;
	            }   
	            in.close();
	    } catch (IOException e) {
	    }
	}
	
	public void adicionarProcessador() {
		try {
	        BufferedReader in = new BufferedReader(new FileReader("processador.txt"));
	            String str;
	            String entrada[];
	            int i=0;
	            while (in.ready()) {
	                str = in.readLine();
	                entrada = str.split(" ");
	                try {
						this.stm.executeUpdate("INSERT INTO Processador VALUES (" +
							(i) + "," +
							"\"" + entrada[0] + "\","+ 
							"\"" + entrada[1] + "\"," +
							"\"" + entrada[2] + "\"," +
							entrada[3] + "," +
							entrada[4] + "" +
							")");
	               		} catch (SQLException e) {
	               			e.printStackTrace();
					}
	                i++;
	            }   
	            in.close();
	    } catch (IOException e) {
	    }
	}
	
	public void adicionarPlacaMae() {
		try {
	        BufferedReader in = new BufferedReader(new FileReader("placaMae.txt"));
	            String str;
	            int i=0;
	            String entrada[];
	            while (in.ready()) {
	            	
	            	str = in.readLine();
	                entrada = str.split(" ");
	                try {
						this.stm.executeUpdate("INSERT INTO PlacaMae VALUES (" +
							(i) + "," +
							"\"" + entrada[0] + "\","+ 
							"\"" + entrada[1] + "\"," +
							"\"" + entrada[2] + "\"," +
							entrada[3] + "," +
							"\"" + entrada[4] + "\"," +
							"\"" + entrada[5] + "\"" +
							")");
	               		} catch (SQLException e) {
							e.printStackTrace();
						}
	                i++;
	            }
	                
	            in.close();
	    } catch (IOException e) {
	    }
	}

	public String retornaSocket(String modelo){
		ResultSet rs;
		String socket = "";
		try {
			rs = this.stm.executeQuery("SELECT * FROM PlacaMae WHERE modelo=\""+ modelo +"\"");
			while(rs.next()){
				if (tipo.equals("Processador"))	socket = rs.getString("socketPro");
				else if(tipo.equals("PlacaVideo")) socket = rs.getString("socketPv");
			}
			rs.close();
		
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return socket;
}
	
	public List<String> retornaListaFabricante(String marca){
		List<String> lista = new ArrayList<String>();
		ResultSet rs;
		try {
			rs = this.stm.executeQuery("SELECT * FROM PlacaMae WHERE marca=\""+ marca +"\" GROUP BY fabricante");
			while(rs.next()){
				lista.add(rs.getString("fabricante"));
			}
			rs.close();
		
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return lista;
	}
	
	public List<String> retornaListaPreco(String socket, double inicio, double fim){
		List<String> lista = new ArrayList<String>();
		ResultSet rs;
		try {
			rs = this.stm.executeQuery("SELECT * FROM "+ tipo +" WHERE socket=\""+ socket +"\" AND preco BETWEEN " + inicio + " AND "+ fim);
			while(rs.next()){
				lista.add(rs.getString("preco"));
			}
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return lista;
	}
	
	public List<String> retornaListaQualidade(String socket, double inicio, double fim){
		List<String> lista = new ArrayList<String>();
		ResultSet rs;
		try {
			rs = this.stm.executeQuery("SELECT * FROM "+ tipo +" WHERE socket=\""+ socket +"\" AND preco BETWEEN " + inicio + " AND "+ fim);
			while(rs.next()){
				lista.add(rs.getString("qualidade"));
			}
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return lista;
	}
	
	public List<String> retornaListaModelo(String socket, double inicio, double fim){
		List<String> lista = new ArrayList<String>();
		ResultSet rs;
		try {
			rs = this.stm.executeQuery("SELECT * FROM "+ tipo +" WHERE socket=\""+ socket +"\" AND preco BETWEEN " + inicio + " AND "+ fim);
			while(rs.next()){
				lista.add(rs.getString("modelo"));
			}
			rs.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		return lista;
	}

	public List<String> retornaModelos(String fabricante, String marca){
		
		List<String> lista = new ArrayList<String>();
		
		ResultSet rs;
		try {
			rs = this.stm.executeQuery("SELECT * FROM PlacaMae WHERE marca=\""+ marca +"\" and fabricante =\""+ fabricante + "\"  ");
			while(rs.next()){
				lista.add(rs.getString("modelo"));
			}
			rs.close();
			
			/*
			for(int i=0;i<lista.size();i++){
				System.out.println(lista.get(i));
			}
			*/
			
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return lista;
	}
}
